/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.googlecode.jsimplemarket.model;

import com.googlecode.jsimplemarket.model.Creanta;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 *
 * @author Alexandru.Codescu
 */
@Entity
@Table(name = "inchirieri")
@NamedQueries({
    @NamedQuery(
            name = "Inchiriere.querySumIncasariByData",
            query = "select sum(t.valoare), count(t.id)  "
                    + "from Inchiriere i "
                    + "join i.taxe t "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 "),
                    //+ "and i.dataSfarsit <= ?2 "),
    @NamedQuery(
            name = "Inchiriere.querySumIncasariByDataTZ",
            query = "select sum(t.valoare), count(t.id)  "
                    + "from Inchiriere i "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 "
                    + "and tt.id = 1 "),
    @NamedQuery(
            name = "Inchiriere.querySumIncasariByDataAA",
            query = "select sum(t.valoare), count(t.id)  "
                    + "from Inchiriere i "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 "
                    + "and tt.id <> 1 "),
    @NamedQuery(
            name = "Inchiriere.querySumIncasariByDataPlata",
            query = "select sum(t.valoare), count(t.id)  "
                    + "from Inchiriere i "
                    + "join i.creanta c "
                    + "join c.plati p "
                    + "join i.taxe t "
                    + "where p.dataPlata = ?1 "
                    + "and c.dataInceput <> p.dataPlata"),
    @NamedQuery(
            name = "Inchiriere.queryIncasariByData",
            query = "select tt.denumire, t.valoare, count(i) "
                    + "from Inchiriere i "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 "
                    //+ "and i.dataSfarsit <= ?2 "
                    + "group by tt.denumire, t.valoare"),
   @NamedQuery(
           name = "Inchiriere.queryIncasariByDataPlata",
           query = "select tt.denumire, t.valoare, count(i), t.valoare*count(i) "
                   + "from Inchiriere i "
                   + "join i.creanta c "
                   + "join c.plati p "
                   + "join i.taxe t "
                   + "join t.tipTaxa tt "
                   + "where p.dataPlata = ?1 "
                   + "and c.dataInceput <> p.dataPlata "
                   //+ "and i.dataInceput <= ?2 "
                   //+ "and i.dataSfarsit <= ?2 "
                   + "group by tt.denumire, t.valoare"),
     @NamedQuery(
           name = "Inchiriere.queryIncasariByDataPlataDetailed",
           query = "select i.id, i.dataInceput, sc.id, sc.cod, f.id, f.denumire, t.valoare, p.dataPlata "
                   + "from Inchiriere i "
                   + "join i.creanta c "
                   + "join c.plati p "
                   + "left join i.firma f "
                   + "join i.spatiuComercial sc "
                   + "join i.taxe t "
                   + "join t.tipTaxa tt "
                   + "where p.dataPlata = ?1 "
                   + "and c.dataInceput <> p.dataPlata "
                   + "order by sc.id asc"),
                   //+ "and i.dataInceput <= ?2 "
                   //+ "and i.dataSfarsit <= ?2 "
                   //+ "group by tt.denumire, t.valoare"),
    @NamedQuery(
            name = "Inchiriere.queryTarabeByData",
            query = "select i.id, i.dataInceput, sc.id, sc.cod, f.id, f.denumire, t.valoare, p.dataPlata, tt.id  "
                    + "from Inchiriere i "
                    + "join i.creanta c "
                    + "join c.plati p "
                    + "left join i.firma f "
                    + "join i.spatiuComercial sc "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 "
                    + "and i.dataInceput <= ?2 "
                    + "and tt.id = 1 "
                    + "order by sc.id asc, i.dataInceput asc"),
    @NamedQuery(
            name = "Inchiriere.queryTarabeByDataAA",
            query = "select i.id, i.dataInceput, sc.id, sc.cod, f.id, f.denumire, t.valoare, p.dataPlata, tt.id  "
                    + "from Inchiriere i "
                    + "join i.creanta c "
                    + "join c.plati p "
                    + "left join i.firma f "
                    + "join i.spatiuComercial sc "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 "
                    + "and tt.id <> 1 "
                    + "order by sc.id asc, i.dataInceput asc"),
    @NamedQuery(
            name = "Inchiriere.queryInchirieriByTarabaByDay",
            query = "select i.id, i.dataInceput, sc.id, sc.cod, f.id, f.denumire, t.valoare "
                    + "from Inchiriere i "
                    + "left join i.firma f "
                    + "join i.spatiuComercial sc "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.dataInceput >= ?1 "
                    + "and i.dataInceput <= ?2 " +
                    "and sc.id = ?3 " +
                    "and tt.id = 1"),
     @NamedQuery(
            name = "Inchiriere.queryFirmaByTaraba",
            query = "select f.denumire, f.dateContact, sc.cod, f.id, t.denumireStare, count(i), sum(c.valoare), max(p.dataPlata)  "
                    + "from Inchiriere i "
                    + "join i.creanta c "
                    + "join c.plati p "
                    + "join i.firma f "
                    + "join i.spatiuComercial sc "
                    + "join i.taxe t "
                    + "join t.tipTaxa tt "
                    + "where i.firma is not null "
                    + "and sc.id = ?1 "
                    + "and i.dataInceput >= ?2 "
                    + "and i.dataInceput <= ?3 "
                    + "and tt.id in (2,3,4) "
                    + "group by sc.cod")
                    //+ "order by i.dataInceput desc")
            
})
public class Inchiriere extends AbstractEntitate {
    private SpatiuComercial spatiuComercial;
    private Firma firma;
    private Date dataInceput;
    private Date dataSfarsit;
    private List<ObservatieInchiriere> observatii;
    private List<Taxa> taxe;
    private Creanta creanta;

    @OneToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "ID_SPATIU_COMERCIAL")
    public SpatiuComercial getSpatiuComercial() {
        return spatiuComercial;
    }

    public void setSpatiuComercial(SpatiuComercial spatiuComercial) {
        this.spatiuComercial = spatiuComercial;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_FIRMA")
    public Firma getFirma() {
        return firma;
    }

    public void setFirma(Firma firma) {
        this.firma = firma;
    }

    @Column(name = "DATA_INCEPUT", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    public Date getDataInceput() {
        return dataInceput;
    }

    public void setDataInceput(Date dataInceput) {
        this.dataInceput = dataInceput;
    }

    @Column(name = "DATA_SFARSIT", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    public Date getDataSfarsit() {
        return dataSfarsit;
    }

    public void setDataSfarsit(Date dataSfarsit) {
        this.dataSfarsit = dataSfarsit;
    }

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_INCHIRIERE")
    public List<ObservatieInchiriere> getObservatii() {
        return observatii;
    }

    public void setObservatii(List<ObservatieInchiriere> observatii) {
        this.observatii = observatii;
    }

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "taxe_inchirieri", joinColumns =
    @JoinColumn(name = "TAXA_ID"), inverseJoinColumns =
    @JoinColumn(name = "INCHIRIERE_ID"))
    public List<Taxa> getTaxe() {
        return taxe;
    }

    public void setTaxe(List<Taxa> taxe) {
        this.taxe = taxe;
    }

    @OneToOne(fetch = FetchType.LAZY, mappedBy = "inchiriere")
    public Creanta getCreanta() {
        return creanta;
    }

    public void setCreanta(Creanta creanta) {
        this.creanta = creanta;
    }
    
    
}
